package com.hy.lightning.ds.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.hy.lightning.ds.JdbcDataSource;
import com.hy.lightning.ds.SqlSession;
import com.hy.lightning.ds.jdbc.datasources.HikariCPDataSource;
import com.hy.lightning.ds.jdbc.datasources.JdbcConf;



public class SprocSqlSession implements SqlSession {

	Logger logger = Logger.getLogger(SprocSqlSession.class);
	
	private static final JdbcDataSource jdbcDataSource = new HikariCPDataSource();

	private String dataSourceKey;
	
	private BaseSqlSession baseSqlSession;
	
	public SprocSqlSession() {
		this("local");
	}
	
	public SprocSqlSession(String dataSourceKey) {
		this.dataSourceKey = dataSourceKey;
		this.baseSqlSession = new BaseSqlSession(dataSourceKey);
	}

	public static void loadSprocSqlFile(String dataSourceKey, List<String> sqlFilePaths){
		for(String sqlFilePath: sqlFilePaths) {
			InputStream in = ClassLoader.getSystemResourceAsStream(sqlFilePath);
			Connection conection = jdbcDataSource.getConnection(dataSourceKey);
			SqlExpc.execute(conection, in);
		}
	}
	
	/********************************************************** 私有方法 *********************************************************************/
	/**
	 * 获取Statement对象
	 * @throws SQLException 
	 */
	private CallableStatement getCallableStatement(Connection conection, String methodName, Object... params) throws SQLException {
		String sql = String.format("{CALL %s}", methodName);
		if(params != null && params.length > 0) {
			String paramStr = "";
			for(int i = 0; i < params.length; i++) {
				paramStr += ",?";
			}
			paramStr = paramStr.substring(1);
			sql = String.format("{CALL %s(%s)}", methodName, paramStr);
		}
		CallableStatement callableStatement = conection.prepareCall(sql);
		if(params == null || params.length == 0) return callableStatement;
		for (int i = 0; i < params.length; i++) {
			Object object = params[i];
			callableStatement.setObject(i + 1, object);
		}
		return callableStatement;
	}
	
	/**
	 * 查询数据集合
	 */
	@Override
	public List<Map<String,Object>> select(String methodName, Object ...params) {
		if(methodName.trim().contains(" ")) return baseSqlSession.select(methodName, params);
		CallableStatement callableStatement = null;
		ResultSet resultSet = null;
		List<Map<String, Object>> list = null;
		Connection conection = jdbcDataSource.getConnection(dataSourceKey);
		try {
			callableStatement = getCallableStatement(conection, methodName, params);
			resultSet = callableStatement.executeQuery();
			list = new ArrayList<>();
			ResultSetMetaData metaData = resultSet.getMetaData();
			while (resultSet.next()) {
				Map<String, Object> resultMap = new HashMap<>();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					String key = metaData.getColumnLabel(i + 1);
					Object value = resultSet.getObject(i + 1);
					resultMap.put(key, value);
				}
				list.add(resultMap);
			}
		} catch (SQLException e) {
			logger.error(e, e);
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (callableStatement != null)
					callableStatement.close();
				if (conection != null)
					conection.close();
			} catch (SQLException e) {

			}
		}
		return list;
	}
	
	@Override
	public Map<String, Object> selectOne(String methodName, Object... params) {
		List<Map<String, Object>> list = select(methodName, params);
		if (list == null || list.size() == 0)
			return null;
		return list.get(0);
	}
	
	@Override
	public int update(String methodName, Object... params) {
		if(methodName.trim().contains(" ")) return baseSqlSession.update(methodName, params);
		CallableStatement callableStatement = null;
		int relsult = 0;
		Connection conection = jdbcDataSource.getConnection(dataSourceKey);
		try {
			callableStatement = getCallableStatement(conection, methodName, params);
			relsult = callableStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e, e);
			e.printStackTrace();
		} finally {
			try {
				if (callableStatement != null)
					callableStatement.close();
				if (conection != null)
					conection.close();
			} catch (SQLException e) {

			}
		}
		return relsult;
	}

	@Override
	public int getCount(String methodName, Object... params) {
		Map<String, Object> map = selectOne(methodName, params);
		if (map == null) return 0;
		for (String key : map.keySet()) {
			if (map.get(key) != null) return Integer.valueOf(map.get(key).toString());
		}
		return 0;
	}

	
	@Override
	public int insert(String tableName, Map<String, Object> map) {
		// TODO Auto-generated method stub
		return baseSqlSession.insert(tableName, map);
	}

	@Override
	public int insertBatch(String tableName, List<Map<String, Object>> list) {
		// TODO Auto-generated method stub
		return baseSqlSession.insertBatch(tableName, list);
	}

	@Override
	public int update(String tableName, Map<String, Object> map) {
		// TODO Auto-generated method stub
		return baseSqlSession.update(tableName, map);
	}

	@Override
	public int updateBatch(String tableName, List<Map<String, Object>> list) {
		// TODO Auto-generated method stub
		return baseSqlSession.updateBatch(tableName, list);
	}

	@Override
	public int delete(String tableName, Map<String, Object> map) {
		// TODO Auto-generated method stub
		return baseSqlSession.delete(tableName, map);
	}

	@Override
	public int deleteBatch(String tableName, List<Map<String, Object>> list) {
		// TODO Auto-generated method stub
		return baseSqlSession.deleteBatch(tableName, list);
	}

	public static void main(String[] args) throws IOException {
		JdbcConf jdbcConf = new JdbcConf("local","120.77.93.39", "car_wash", "root", "49BA59ABBE56E057");
		JdbcDataSource.initDataSource(jdbcConf);
		SprocSqlSession session = new SprocSqlSession();
		SprocSqlSession.loadSprocSqlFile("local", Arrays.asList("com/dc/car_wash/core/jdbc/sproc/Sproc.sql"));
		System.out.println(session.selectOne("user_getUserIdByMobile", "test"));
	}
}
